const router = require('koa-router')()
var svgCaptcha = require('svg-captcha');
const jwt = require("jsonwebtoken")
const mykey = "djxdjxdjx"
const formidable = require("formidable")
const path = require('path')
const fs = require('fs')
const db = require('../db/dbmanager')
const dirPath = path.join(path.resolve(__dirname, '../'), '/public/images')

router.get('/', async (ctx, next) => {
  await ctx.render('index', {
    title: 'Hello Koa 2!'
  })
})

router.post("/captcha", async (ctx, next) => {
  var captcha = svgCaptcha.create();
  ctx.session.captcha = captcha.text;
  ctx.body = captcha.data;
})

router.post("/login", async (ctx, next) => {
  let user = ctx.request.body
  let { userName, password } = user;
  console.log(user);
  let message = {}
  let sql = 'select * from users where userName=? and password=?'
  let result = ctx.db.EXCUTE(sql, [userName, password]);
  console.log(result);
  if (result.length == 0) {
    message = { code: 400 }
  } else {
    const payload = { //定义token的有限载荷
      name: result.username
    }
    const secret = 'deyun'
    const token = jwt.sign({ userName, password }, payload, secret, mykey)
    message = { code: 200, token: token, success: true }
  }
  ctx.body = message;
})

router.post('/register', async (ctx, next) => {
  let user = ctx.request.body
  // console.log(user);
  let sql1 = 'select * from users where userName = ?'
  let sql2 = 'insert into users (userName, password) value (?, ?)'
  let result = await ctx.db.EXCUTE(sql1, [user.userName])
  console.log(result);
  if (result.length > 0) {
    message = { code: 400 }
  }
  else {
    await ctx.db.EXCUTE(sql2, [user.userName, user.password])
    message = { code: 200 }
  }
  ctx.body = message;
})

// 查询显示用户信息
router.post('/putuser', async (ctx, next) => {
  let sql = 'select * from adduser'
  let usersList = await ctx.db.EXCUTE(sql);
  ctx.body = { List: usersList };
})

// 查询准确用户信息
router.post('/seluser', async (ctx, next) => {
  let user = ctx.request.body
  let { userName, phoneNum, mailbox, place } = user
  console.log(user);
  let sql = 'select * from adduser where userName = ?'
  let result = await ctx.db.EXCUTE(sql, [userName, phoneNum, mailbox, place])
  console.log(result);
  if (result.length == 0) {
    message = { code: 400 }
  }
  else {
    message = { code: 200, List: result }
  }
  ctx.body = message;
})

// 增加用户信息
router.post('/adduser', async (ctx, next) => {
  let user = ctx.request.body
  let { userName, phoneNum, mailbox, place } = user
  console.log(user);
  let sql1 = 'select * from adduser where userName = ?'
  let sql2 = 'insert into adduser (userName,phoneNum,mailbox,place) value (?,?,?,?)'
  let result = await ctx.db.EXCUTE(sql1, [userName])
  console.log(result);
  if (result.length > 0) {
    message = { code: 400 }
  }
  else {
    await ctx.db.EXCUTE(sql2, [userName, phoneNum, mailbox, place])
    message = { code: 200 }
  }
  ctx.body = message;
})

// 删除用户信息
router.post('/deluser', async (ctx, next) => {
  let user = ctx.request.body
  console.log(user);
  let { userName } = user;
  let message = {}
  let sql1 = 'select * from adduser where userName=?'
  let sql = 'delete  from adduser where userName=?'
  let result = await ctx.db.EXCUTE(sql1, [userName])
  console.log(result);
  if (result.length = 0) {
    message = { code: 401 }
  } else {
    await ctx.db.EXCUTE(sql, [userName], async (err, data) => {
      console.log(data);
      if (err) {
        message = { code: 400 }
      }
      if (data.affectedRows > 0) {
        message = { code: 200 }
      }
      else { message = { code: 202 } }
    })
  }
  ctx.body = message
})

// 修改用户信息
router.post('/edituser', async (ctx, next) => {
  let user = ctx.request.body
  let { userId, userName, phoneNum, mailbox, place } = user
  console.log(user);
  let sql1 = 'select * from adduser where userId = ?'
  let sql2 = 'update adduser set userName=?, phoneNum=?,mailbox=?,place=? where userId=?'
  let result = await ctx.db.EXCUTE(sql1, [userId])
  console.log(result);
  if (result.length == 0) {
    message = { code: 400 }
  }
  else {
    let results = await ctx.db.EXCUTE(sql2, [userName, phoneNum, mailbox, place, userId])
    message = { code: 200, List: results }
    console.log(results)
  }
  ctx.body = message;
})

//获取用户分页
router.get('/getUserData', (req, res) => {
  const sqlStr = `SELECT * FROM adduser`;
  connection.query(sqlStr, (error, results, fields) => {
    if (error) {
      res.json({ err_code: 0, message: "获取失败" });
    } else {
      results = JSON.parse(JSON.stringify(results));
      res.json({ success_code: 200, data: results })
    }
  })
})


//用户分页查询
router.post('/pageuser', (req, res) => {
  let pageNo = Number(req.body.page) || 1;
  let pageCount = Number(req.body.pageSize) || 10;
  let pageSize = pageCount;
  let page = (pageNo - 1) * pageCount;
  console.log(page, pageSize);
  let sqlStr = `select * from adduser limit ${page},${pageSize}`;
  connection.query(sqlStr, (error, results, fields) => {
    if (error) {
      console.log(error);
      res.json({ err_code: 0, message: "获取分页数据失败" });
    } else {
      console.log(results);
      res.json({ success_code: 200, message: '获取分页数据成功', data: results })
    }
  })
})

//获取物流分页
router.get('/getWuliuData', (req, res) => {
  const sqlStr = `SELECT * FROM wuliu`;
  connection.query(sqlStr, (error, results, fields) => {
    if (error) {
      res.json({ err_code: 0, message: "获取失败" });
    } else {
      results = JSON.parse(JSON.stringify(results));
      res.json({ success_code: 200, data: results })
    }
  })
})

//物流分页查询
router.post('/pagewuliu', (req, res) => {
  let pageNo = Number(req.body.page) || 1;
  let pageCount = Number(req.body.pageSize) || 10;
  let pageSize = pageCount;
  let page = (pageNo - 1) * pageCount;
  console.log(page, pageSize);
  let sqlStr = `select * from wuliu limit ${page},${pageSize}`;
  connection.query(sqlStr, (error, results, fields) => {
    if (error) {
      console.log(error);
      res.json({ err_code: 0, message: "获取分页数据失败" });
    } else {
      console.log(results);
      res.json({ success_code: 200, message: '获取分页数据成功', data: results })
    }
  })
})

// 查询显示物流信息
router.post('/putwuliu', async (ctx, next) => {
  let sql = 'select * from wuliu'
  let wuliuList = await ctx.db.EXCUTE(sql);
  ctx.body = { List: wuliuList };
})

// 查询准确物流信息
router.post('/selwuliu', async (ctx, next) => {
  let wuliu = ctx.request.body
  let { name, approach, address, phoneNum } = wuliu
  console.log(wuliu);
  let sql = 'select * from wuliu where name = ?'
  let result = await ctx.db.EXCUTE(sql, [name, approach, address, phoneNum])
  console.log(result);
  if (result.length == 0) {
    message = { code: 400 }
  }
  else {
    message = { code: 200, List: result }
  }
  ctx.body = message;
})

// 增加物流信息
router.post('/addwuliu', async (ctx, next) => {
  let wuliu = ctx.request.body
  let { name, approach, address, phoneNum } = wuliu
  console.log(wuliu);
  let sql1 = 'select * from wuliu where name = ?'
  let sql2 = 'insert into wuliu (name,approach,address,phoneNum) value (?,?,?,?)'
  let result = await ctx.db.EXCUTE(sql1, [name])
  console.log(result);
  if (result.length > 0) {
    message = { code: 400 }
  }
  else {
    await ctx.db.EXCUTE(sql2, [name, approach, address, phoneNum])
    message = { code: 200 }
  }
  ctx.body = message;
})

// 删除物流信息
router.post('/delwuliu', async (ctx, next) => {
  let wuliu = ctx.request.body
  console.log(wuliu);
  let { name } = wuliu;
  let message = {}
  let sql1 = 'select * from wuliu where name=?'
  let sql = 'delete from wuliu where name=?'
  let result = await ctx.db.EXCUTE(sql1, [name])
  console.log(result);
  if (result.length = 0) {
    message = { code: 401 }
  } else {
    await ctx.db.EXCUTE(sql, [name], async (err, data) => {
      console.log(data);
      if (err) {
        message = { code: 400 }
      }
      if (data.affectedRows > 0) {
        message = { code: 200 }
      }
      else { message = { code: 202 } }
    })
  }
  ctx.body = message
})

// 修改物流信息
router.post('/editwuliu', async (ctx, next) => {
  let wuliu = ctx.request.body
  let { wuliuId, name, approach, address, phoneNum } = wuliu
  console.log(wuliu);
  let sql1 = 'select * from wuliu where wuliuId = ?'
  let sql2 = 'update wuliu set name=?, approach=?,address=?,phoneNum=? where wuliuId=?'
  let result = await ctx.db.EXCUTE(sql1, [wuliuId])
  console.log(result);
  if (result.length == 0) {
    message = { code: 400 }
  }
  else {
    let results = await ctx.db.EXCUTE(sql2, [name, approach, address, phoneNum, wuliuId])
    message = { code: 200, List: results }
    console.log(results)
  }
  ctx.body = message;
})

router.get('/wuliu', async (ctx, next) => {

})

router.post('/uploadimg', async (ctx, next) => {
  ctx.body = await uploadImg(ctx);
})

// 查询显示图片信息
router.post('/putimage', async (ctx, next) => {
  let sql = 'select * from image'
  let fileList = await ctx.db.EXCUTE(sql);
  ctx.body = { List: fileList };
})

// 图片上传
const uploadImg = (ctx) => {
  const form = formidable({
    multiples: true,
    uploadDir: dirPath,
    keepExtensions: true,
    allowEmptyFiles: false
  });
  return new Promise((resolve, reject) => {
    form.parse(ctx.req, (err, fields, files) => {
      if (err) {
        reject(err);
        return;
      } else {
        let keys = Object.keys(files);
        keys?.map(async (img) => {
          let name = files[img].name;
          let url = files[img].url;
          let size = files[img].size;
          let sql = "insert into image (url,name,size)values(?,?,?)"
          let result = await ctx.db.EXCUTE(sql, [url, name, size]);
          return result;
        })
        resolve({ ok: true, fields, files });
      }
    });
  });
}

// 删除图片信息
router.post('/delimage', async (ctx, next) => {
  let image = ctx.request.body
  console.log(image);
  let { name } = image;
  let message = {}
  let sql1 = 'select * from image where name=?'
  let sql = 'delete from image where name=?'
  let result = await ctx.db.EXCUTE(sql1, [name])
  console.log(result);
  if (result.length = 0) {
    message = { code: 401 }
  } else {
    await ctx.db.EXCUTE(sql, [name], async (err, data) => {
      console.log(data);
      if (err) {
        message = { code: 400 }
      }
      if (data.affectedRows > 0) {
        message = { code: 200 }
      }
      else { message = { code: 202 } }
    })
  }
  ctx.body = message
})

//获取图片分页
router.get('/getImageData', (req, res) => {
  const sqlStr = `SELECT * FROM image`;
  connection.query(sqlStr, (error, results, fields) => {
    if (error) {
      res.json({ err_code: 0, message: "获取失败" });
    } else {
      results = JSON.parse(JSON.stringify(results));
      res.json({ success_code: 200, data: results })
    }
  })
})

//图片分页查询
router.post('/pageimage', (req, res) => {
  let pageNo = Number(req.body.page) || 1;
  let pageCount = Number(req.body.pageSize) || 10;
  let pageSize = pageCount;
  let page = (pageNo - 1) * pageCount;
  console.log(page, pageSize);
  let sqlStr = `select * from image limit ${page},${pageSize}`;
  connection.query(sqlStr, (error, results, fields) => {
    if (error) {
      console.log(error);
      res.json({ err_code: 0, message: "获取分页数据失败" });
    } else {
      console.log(results);
      res.json({ success_code: 200, message: '获取分页数据成功', data: results })
    }
  })
})

router.get('/upload', async (ctx, next) => {
  let token = ctx.req.headers.authorization;
  let result = jwt.verify(token, mykey);
  console.log(result);
  ctx.body = await uploadImg(ctx);
})

router.get('/getpage', async (ctx, next) => {
  let page = fs.readdirSync(dirPath);
  ctx.body = page;
})

router.post('/verify', async (ctx, next) => {
  let body = JSON.parse(ctx.request.body);
  let name = body.name;
  let id = body.id;
  if (name && id) {
    let token = jwt.sign({ name: name }, mykey);
    ctx.body = { token: token }
  }
})

router.get('/string', async (ctx, next) => {
  ctx.body = 'koa2 string'
})

router.get('/json', async (ctx, next) => {
  ctx.body = {
    title: 'koa2 json'
  }
})

module.exports = router
